from django.shortcuts import render, get_object_or_404
from django.db.models import Q
from .models import Stock, StockDetail, DailyData, FinancialIndicator
from django.core.paginator import Paginator
from stocks.services.strategy_service import StrategyService

def stock_list(request):
    query = request.GET.get('q', '')
    stocks = Stock.objects.all()
    
    if query:
        stocks = stocks.filter(
            Q(symbol__icontains=query) | 
            Q(name__icontains=query)
        )
    
    paginator = Paginator(stocks, 50)  # 每页显示50条
    page_number = request.GET.get('page')
    page_obj = paginator.get_page(page_number)
    
    context = {
        'page_obj': page_obj,
        'query': query,
    }
    return render(request, 'stocks/stock_list.html', context)

def stock_detail(request, symbol):
    stock = get_object_or_404(Stock, symbol=symbol)
    try:
        detail = StockDetail.objects.get(symbol=symbol)
    except StockDetail.DoesNotExist:
        detail = None
    
    # 使用stock_daily_view视图查询最近30天的交易数据
    from django.db import connections
    
    sql_query = """
        SELECT id, symbol, trade_date, open, close, high, low, volume, amount, 
               price_amplitude, change_pct, change_amount, turnover_rate
        FROM stock_daily_view
        WHERE symbol = %s
        ORDER BY trade_date DESC 
        LIMIT 30
    """
    
    with connections['postgres'].cursor() as cursor:
        try:
            cursor.execute(sql_query, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                daily_data = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
            else:
                daily_data = []
        except Exception as e:
            # 如果查询出错，返回空数据
            daily_data = []
            print(f"Error querying daily data: {e}")
            print(f"SQL Query: {sql_query}")
            # 打印更详细的错误信息
            import traceback
            traceback.print_exc()
    
    # 使用原始SQL查询最近的财务指标
    from django.db import connections
    
    financial_sql = """
        SELECT stock_code, date, diluted_eps, weighted_eps, roe, 
               net_profit_margin_on_sales, gross_profit_margin, asset_liability_ratio,
               current_ratio, quick_ratio, total_assets, 
               main_business_income_growth_rate, net_profit_growth_rate
        FROM stock_financial_analysis_indicator
        WHERE stock_code = %s
        ORDER BY date DESC
        LIMIT 10
    """
    
    with connections['postgres'].cursor() as cursor:
        try:
            cursor.execute(financial_sql, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                financial_data = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
            else:
                financial_data = []
        except Exception as e:
            # 如果查询出错，返回空数据
            financial_data = []
            print(f"Error querying financial data: {e}")
    
    # 查询最新的股东信息
    from django.db import connections
    
    shareholder_sql = """
        SELECT date, shareholder_name, shareholder_type, share_number, 
               share_ratio, change_type, change_number, change_ratio
        FROM stock_gdfx_holding_analyse_em
        WHERE stock_code = %s
        ORDER BY date DESC, share_ratio DESC
        LIMIT 15
    """
    
    with connections['postgres'].cursor() as cursor:
        try:
            cursor.execute(shareholder_sql, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                shareholder_data = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
            else:
                shareholder_data = []
        except Exception as e:
            # 如果查询出错，返回空数据
            shareholder_data = []
            print(f"Error querying shareholder data: {e}")
    
    context = {
        'stock': stock,
        'detail': detail,
        'daily_data': daily_data,
        'financial_data': financial_data,
        'shareholder_data': shareholder_data,
    }
    return render(request, 'stocks/stock_detail.html', context)

def daily_data(request, symbol):
    stock = get_object_or_404(Stock, symbol=symbol)
    
    # 使用stock_daily_view视图查询所有数据
    from django.db import connections
    
    sql_query = """
        SELECT id, symbol, trade_date, open, close, high, low, volume, amount, 
               price_amplitude, change_pct, change_amount, turnover_rate
        FROM stock_daily_view
        WHERE symbol = %s
        ORDER BY trade_date DESC
    """
    
    with connections['postgres'].cursor() as cursor:
        try:
            cursor.execute(sql_query, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                daily_data_list = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
            else:
                daily_data_list = []
        except Exception as e:
            # 如果查询出错，返回空数据
            daily_data_list = []
            print(f"Error querying daily data: {e}")
            print(f"SQL Query: {sql_query}")
            # 打印更详细的错误信息
            import traceback
            traceback.print_exc()
    
    # 手动分页
    from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
    
    paginator = Paginator(daily_data_list, 100)  # 每页100条
    page_number = request.GET.get('page')
    
    try:
        page_obj = paginator.get_page(page_number)
    except PageNotAnInteger:
        page_obj = paginator.get_page(1)
    except EmptyPage:
        page_obj = paginator.get_page(paginator.num_pages)
    
    context = {
        'stock': stock,
        'page_obj': page_obj,
    }
    return render(request, 'stocks/daily_data.html', context)

def financial_data(request, symbol):
    stock = get_object_or_404(Stock, symbol=symbol)
    
    # 使用原始SQL查询财务数据
    from django.db import connections
    
    sql_query = """
        SELECT stock_code, date, diluted_eps, weighted_eps, roe, 
               net_profit_margin_on_sales, gross_profit_margin, asset_liability_ratio,
               current_ratio, quick_ratio, total_assets, 
               main_business_income_growth_rate, net_profit_growth_rate
        FROM stock_financial_analysis_indicator
        WHERE stock_code = %s
        ORDER by date DESC
    """
    
    with connections['postgres'].cursor() as cursor:
        try:
            cursor.execute(sql_query, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                financial_data_list = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
            else:
                financial_data_list = []
        except Exception as e:
            # 如果查询出错，返回空数据
            financial_data_list = []
            print(f"Error querying financial data: {e}")
    
    # 手动分页
    from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
    
    paginator = Paginator(financial_data_list, 50)  # 每页50条
    page_number = request.GET.get('page')
    
    try:
        page_obj = paginator.get_page(page_number)
    except PageNotAnInteger:
        page_obj = paginator.get_page(1)
    except EmptyPage:
        page_obj = paginator.get_page(paginator.num_pages)
    
    context = {
        'stock': stock,
        'page_obj': page_obj,
    }
    return render(request, 'stocks/financial_data.html', context)

def home(request):
    # 首页显示一些统计信息
    total_stocks = Stock.objects.count()
    latest_stocks = Stock.objects.all()[:10]
    
    context = {
        'total_stocks': total_stocks,
        'latest_stocks': latest_stocks,
    }
    return render(request, 'stocks/home.html', context)


def stock_signals(request, symbol):
    """股票信号详情页面"""
    stock = get_object_or_404(Stock, symbol=symbol)
    
    # 获取筛选参数
    days = request.GET.get('days', '30')
    min_strength = request.GET.get('min_strength', '50')
    strategy_id = request.GET.get('strategy', '')
    
    try:
        days = int(days)
        min_strength = float(min_strength)
    except (ValueError, TypeError):
        days = 30
        min_strength = 50
    
    # 获取该股票的所有信号
    signals = StrategyService.get_stock_all_signals(symbol, days, min_strength, strategy_id)
    
    # 分页处理
    from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
    
    paginator = Paginator(signals, 50)  # 每页50条
    page_number = request.GET.get('page')
    
    try:
        page_obj = paginator.get_page(page_number)
    except PageNotAnInteger:
        page_obj = paginator.get_page(1)
    except EmptyPage:
        page_obj = paginator.get_page(paginator.num_pages)
    
    # 获取统计信息
    total_signals = len(signals)
    avg_strength = sum(s['signal_strength'] for s in signals) / total_signals if total_signals > 0 else 0
    
    # 获取所有策略用于筛选
    from stocks.models import Strategy
    strategies = Strategy.objects.all()
    
    context = {
        'stock': stock,
        'page_obj': page_obj,
        'total_signals': total_signals,
        'avg_strength': avg_strength,
        'days': days,
        'min_strength': min_strength,
        'strategy_id': strategy_id,
        'strategies': strategies,
        'title': f'{stock.symbol} - 买入信号'
    }
    return render(request, 'stocks/stock_signals.html', context)


def stock_visualization(request, symbol):
    """股票数据可视化页面"""
    import json
    stock = get_object_or_404(Stock, symbol=symbol)
    
    # 获取最近60天的交易数据（用于K线图）
    from django.db import connections
    
    # 使用原始SQL查询获取OHLC数据
    sql_query = """
        SELECT trade_date, open, high, low, close, volume
        FROM stock_daily_view
        WHERE symbol = %s
        ORDER BY trade_date DESC
        LIMIT 60
    """
    
    daily_data = []
    try:
        with connections['postgres'].cursor() as cursor:
            cursor.execute(sql_query, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                daily_data = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
                print(f"Found {len(daily_data)} daily records for {symbol}")
            else:
                print(f"No daily data found for {symbol}")
    except Exception as e:
        print(f"Error querying daily data for {symbol}: {e}")
        import traceback
        traceback.print_exc()
    
    # 反转数据顺序（从旧到新）
    daily_data.reverse()
    
    # 获取财务指标数据
    financial_sql = """
        SELECT date, roe, net_profit_margin_on_sales, gross_profit_margin, 
               asset_liability_ratio, current_ratio, main_business_income_growth_rate
        FROM stock_financial_analysis_indicator
        WHERE stock_code = %s
        ORDER BY date DESC
        LIMIT 8
    """
    
    financial_data = []
    try:
        with connections['postgres'].cursor() as cursor:
            cursor.execute(financial_sql, [symbol])
            if cursor.description:
                columns = [col[0] for col in cursor.description]
                financial_data = [
                    dict(zip(columns, row))
                    for row in cursor.fetchall()
                ]
                print(f"Found {len(financial_data)} financial records for {symbol}")
            else:
                print(f"No financial data found for {symbol}")
    except Exception as e:
        print(f"Error querying financial data for {symbol}: {e}")
        import traceback
        traceback.print_exc()
    
    # 准备K线图数据（OHLC格式）- 确保数据格式正确
    ohlc_data = []
    for data in daily_data:
        # 确保所有必需的字段都存在且不为None
        if all(key in data and data[key] is not None for key in ['trade_date', 'open', 'high', 'low', 'close', 'volume']):
            try:
                ohlc_data.append({
                    'x': data['trade_date'].isoformat(),
                    'o': float(data['open']),
                    'h': float(data['high']),
                    'l': float(data['low']),
                    'c': float(data['close']),
                    'v': int(data['volume'])
                })
            except (ValueError, TypeError) as e:
                print(f"Error converting data for OHLC: {e}, data: {data}")
        else:
            print(f"Incomplete data for OHLC: {data}")
    
    # 转换财务数据为JSON可序列化格式
    serializable_financial_data = []
    for data in financial_data:
        try:
            serializable_financial_data.append({
                'date': data['date'].isoformat() if data.get('date') else None,
                'roe': float(data['roe']) if data.get('roe') is not None else None,
                'net_profit_margin_on_sales': float(data['net_profit_margin_on_sales']) if data.get('net_profit_margin_on_sales') is not None else None,
                'gross_profit_margin': float(data['gross_profit_margin']) if data.get('gross_profit_margin') is not None else None,
                'asset_liability_ratio': float(data['asset_liability_ratio']) if data.get('asset_liability_ratio') is not None else None,
                'current_ratio': float(data['current_ratio']) if data.get('current_ratio') is not None else None,
                'main_business_income_growth_rate': float(data['main_business_income_growth_rate']) if data.get('main_business_income_growth_rate') is not None else None
            })
        except (ValueError, TypeError) as e:
            print(f"Error converting financial data: {e}, data: {data}")
    
    # 将数据转换为JSON字符串，确保安全传递到模板
    try:
        ohlc_data_json = json.dumps(ohlc_data)
        financial_data_json = json.dumps(serializable_financial_data)
    except Exception as e:
        print(f"Error serializing data to JSON: {e}")
        ohlc_data_json = '[]'
        financial_data_json = '[]'
    
    print(f"OHLC data count: {len(ohlc_data)}")
    print(f"Financial data count: {len(serializable_financial_data)}")
    
    context = {
        'stock': stock,
        'ohlc_data': ohlc_data,
        'ohlc_data_json': ohlc_data_json,
        'financial_data': serializable_financial_data,
        'financial_data_json': financial_data_json,
        'daily_data': daily_data,
        'title': f'{stock.symbol} - 数据可视化'
    }
    return render(request, 'stocks/stock_visualization.html', context)
